--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Node / ReticulumProjects / MeshChatX.git / files / meshchatx / src / backend / database / messages.py
Displaying Raw • Download
meshchatx/src/backend/database/messages.py bc8969ab162af244f42eabca93382e30c471f72a (bc8969ab) Text, 15.32 KB
Tff7b72import T7ee787json
Tff7b72from T7ee787datetime Tff7b72import Te6edf3UTCTb4b4b4, Te6edf3datetime
Tff7b72from T7ee787.T7ee787provider Tff7b72import Te6edf3DatabaseProvider
Tff7b72class T56d364MessageDAOTb4b4b4:
Tff7b72def Tff7b72__init__Tb4b4b4(Tff7b72selfTb4b4b4, Te6edf3providerTb4b4b4: Te6edf3DatabaseProviderTb4b4b4)Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffprovider Tff7b72= Te6edf3provider
Tff7b72def Td2a8ffupsert_lxmf_messageTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3dataTb4b4b4)Tb4b4b4:
T8b949e# Ensure data is a dict if it's a sqlite3.Row
Tff7b72if Tff7b72not Tffa657isinstanceTb4b4b4(Te6edf3dataTb4b4b4, Tffa657dictTb4b4b4)Tb4b4b4:
Te6edf3data Tff7b72= Tffa657dictTb4b4b4(Te6edf3dataTb4b4b4)
T8b949e# Ensure all required fields are present and handle defaults
Te6edf3fields Tff7b72= Tb4b4b4[
Ta5d6ff"Ta5d6ffhashTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffsource_hashTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffdestination_hashTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffpeer_hashTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffstateTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffprogressTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffis_incomingTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffmethodTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffdelivery_attemptsTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffnext_delivery_attempt_atTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6fftitleTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffcontentTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6fffieldsTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6fftimestampTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffrssiTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffsnrTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffqualityTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffis_spamTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffreply_to_hashTa5d6ff"Tb4b4b4,
Tb4b4b4]
Te6edf3columns Tff7b72= Ta5d6ff"Ta5d6ff, Ta5d6ff"Tff7b72.Td2a8ffjoinTb4b4b4(Te6edf3fieldsTb4b4b4)
Te6edf3placeholders Tff7b72= Ta5d6ff"Ta5d6ff, Ta5d6ff"Tff7b72.Td2a8ffjoinTb4b4b4(Tb4b4b4[Ta5d6ff"Ta5d6ff?Ta5d6ff"Tb4b4b4] Tff7b72* Tffa657lenTb4b4b4(Te6edf3fieldsTb4b4b4)Tb4b4b4)
Te6edf3update_set Tff7b72= Ta5d6ff"Ta5d6ff, Ta5d6ff"Tff7b72.Td2a8ffjoinTb4b4b4(Tb4b4b4[Ta5d6fffTa5d6ff"Tffd700{Te6edf3fTffd700}Ta5d6ff = EXCLUDED.Tffd700{Te6edf3fTffd700}Ta5d6ff" Tff7b72for Te6edf3f Tff7b72in Te6edf3fields Tff7b72if Te6edf3f Tff7b72!= Ta5d6ff"Ta5d6ffhashTa5d6ff"Tb4b4b4]Tb4b4b4)
Te6edf3query Tff7b72= Tb4b4b4(
Ta5d6fffTa5d6ff"Ta5d6ffINSERT INTO lxmf_messages (Tffd700{Te6edf3columnsTffd700}Ta5d6ff, created_at, updated_at) VALUES (Tffd700{Te6edf3placeholdersTffd700}Ta5d6ff, ?, ?) Ta5d6ff" T8b949e# noqa: S608
Ta5d6fffTa5d6ff"Ta5d6ffON CONFLICT(hash) DO UPDATE SET Tffd700{Te6edf3update_setTffd700}Ta5d6ff, updated_at = EXCLUDED.updated_atTa5d6ff"
Tb4b4b4)
Te6edf3params Tff7b72= Tb4b4b4[Tb4b4b4]
Tff7b72for Te6edf3f Tff7b72in Te6edf3fieldsTb4b4b4:
Te6edf3val Tff7b72= Te6edf3dataTff7b72.Td2a8ffgetTb4b4b4(Te6edf3fTb4b4b4)
Tff7b72if Te6edf3f Tff7b72== Ta5d6ff"Ta5d6fffieldsTa5d6ff" Tff7b72and Tffa657isinstanceTb4b4b4(Te6edf3valTb4b4b4, Tffa657dictTb4b4b4)Tb4b4b4:
Te6edf3val Tff7b72= Te6edf3jsonTff7b72.Td2a8ffdumpsTb4b4b4(Te6edf3valTb4b4b4)
Te6edf3paramsTff7b72.Td2a8ffappendTb4b4b4(Te6edf3valTb4b4b4)
Te6edf3now Tff7b72= Te6edf3datetimeTff7b72.Td2a8ffnowTb4b4b4(Te6edf3UTCTb4b4b4)Tff7b72.Td2a8ffisoformatTb4b4b4(Tb4b4b4)
Te6edf3paramsTff7b72.Td2a8ffappendTb4b4b4(Te6edf3nowTb4b4b4)
Te6edf3paramsTff7b72.Td2a8ffappendTb4b4b4(Te6edf3nowTb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Te6edf3queryTb4b4b4, Te6edf3paramsTb4b4b4)
Tff7b72def Td2a8ffget_lxmf_message_by_hashTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3message_hashTb4b4b4)Tb4b4b4:
Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(
Ta5d6ff"Ta5d6ffSELECT * FROM lxmf_messages WHERE hash = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3message_hashTb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffdelete_lxmf_messages_by_hashesTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3message_hashesTb4b4b4)Tb4b4b4:
Tff7b72if Tff7b72not Te6edf3message_hashesTb4b4b4:
Tff7b72return
Te6edf3placeholders Tff7b72= Ta5d6ff"Ta5d6ff, Ta5d6ff"Tff7b72.Td2a8ffjoinTb4b4b4(Tb4b4b4[Ta5d6ff"Ta5d6ff?Ta5d6ff"Tb4b4b4] Tff7b72* Tffa657lenTb4b4b4(Te6edf3message_hashesTb4b4b4)Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6fffTa5d6ff"Ta5d6ffDELETE FROM lxmf_messages WHERE hash IN (Tffd700{Te6edf3placeholdersTffd700}Ta5d6ff)Ta5d6ff"Tb4b4b4, T8b949e# noqa: S608
Tffa657tupleTb4b4b4(Te6edf3message_hashesTb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffdelete_lxmf_message_by_hashTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3message_hashTb4b4b4)Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffDELETE FROM lxmf_messages WHERE hash = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3message_hashTb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffdelete_all_lxmf_messagesTb4b4b4(Tff7b72selfTb4b4b4)Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Ta5d6ff"Ta5d6ffDELETE FROM lxmf_messagesTa5d6ff"Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Ta5d6ff"Ta5d6ffDELETE FROM lxmf_conversation_read_stateTa5d6ff"Tb4b4b4)
Tff7b72def Td2a8ffget_all_lxmf_messagesTb4b4b4(Tff7b72selfTb4b4b4)Tb4b4b4:
Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchallTb4b4b4(Ta5d6ff"Ta5d6ffSELECT * FROM lxmf_messagesTa5d6ff"Tb4b4b4)
Tff7b72def Td2a8ffcount_lxmf_messagesTb4b4b4(Tff7b72selfTb4b4b4)Tb4b4b4:
Te6edf3row Tff7b72= Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(Ta5d6ff"Ta5d6ffSELECT COUNT(*) AS count FROM lxmf_messagesTa5d6ff"Tb4b4b4)
Tff7b72return Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6ffcountTa5d6ff"Tb4b4b4] Tff7b72if Te6edf3row Tff7b72and Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6ffcountTa5d6ff"Tb4b4b4] Tff7b72is Tff7b72not Tff7b72None Tff7b72else T79c0ff0
Tff7b72def Td2a8ffcount_lxmf_messages_by_stateTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3stateTb4b4b4)Tb4b4b4:
Te6edf3row Tff7b72= Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(
Ta5d6ff"Ta5d6ffSELECT COUNT(*) AS count FROM lxmf_messages WHERE state = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3stateTb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72return Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6ffcountTa5d6ff"Tb4b4b4] Tff7b72if Te6edf3row Tff7b72and Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6ffcountTa5d6ff"Tb4b4b4] Tff7b72is Tff7b72not Tff7b72None Tff7b72else T79c0ff0
Tff7b72def Td2a8ffget_conversation_messagesTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4, Te6edf3limitTff7b72=T79c0ff100Tb4b4b4, Te6edf3offsetTff7b72=T79c0ff0Tb4b4b4)Tb4b4b4:
Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchallTb4b4b4(
Ta5d6ff"Ta5d6ffSELECT * FROM lxmf_messages WHERE peer_hash = ? ORDER BY timestamp DESC LIMIT ? OFFSET ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3destination_hashTb4b4b4, Te6edf3limitTb4b4b4, Te6edf3offsetTb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffget_conversationsTb4b4b4(Tff7b72selfTb4b4b4)Tb4b4b4:
T8b949e# Optimized using peer_hash column
Te6edf3query Tff7b72= Ta5d6ff"""
Ta5d6ff SELECT m1.* FROM lxmf_messages m1
Ta5d6ff INNER JOIN (
Ta5d6ff SELECT peer_hash, MAX(timestamp) as max_ts
Ta5d6ff FROM lxmf_messages
Ta5d6ff WHERE peer_hash IS NOT NULL
Ta5d6ff GROUP BY peer_hash
Ta5d6ff ) m2 ON m1.peer_hash = m2.peer_hash AND m1.timestamp = m2.max_ts
Ta5d6ff GROUP BY m1.peer_hash
Ta5d6ff ORDER BY m1.timestamp DESC
Ta5d6ff Ta5d6ff"""
Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchallTb4b4b4(Te6edf3queryTb4b4b4)
Tff7b72def Td2a8ffmark_conversation_as_readTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4)Tb4b4b4:
Te6edf3now Tff7b72= Te6edf3datetimeTff7b72.Td2a8ffnowTb4b4b4(Te6edf3UTCTb4b4b4)Tff7b72.Td2a8ffisoformatTb4b4b4(Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
T8b949e"""
INSERT INTO lxmf_conversation_read_state (destination_hash, last_read_at, created_at, updated_at)
VALUES (?, ?, ?, ?)
ON CONFLICT(destination_hash) DO UPDATE SET
last_read_at = EXCLUDED.last_read_at,
updated_at = EXCLUDED.updated_at
"""Tb4b4b4,
Tb4b4b4(Te6edf3destination_hashTb4b4b4, Te6edf3nowTb4b4b4, Te6edf3nowTb4b4b4, Te6edf3nowTb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffmark_conversations_as_readTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashesTb4b4b4)Tb4b4b4:
Tff7b72if Tff7b72not Te6edf3destination_hashesTb4b4b4:
Tff7b72return
Te6edf3now Tff7b72= Te6edf3datetimeTff7b72.Td2a8ffnowTb4b4b4(Te6edf3UTCTb4b4b4)Tff7b72.Td2a8ffisoformatTb4b4b4(Tb4b4b4)
Tff7b72with Tff7b72selfTff7b72.Td2a8ffproviderTb4b4b4:
Tff7b72for Te6edf3destination_hash Tff7b72in Te6edf3destination_hashesTb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
T8b949e"""
INSERT INTO lxmf_conversation_read_state (destination_hash, last_read_at, created_at, updated_at)
VALUES (?, ?, ?, ?)
ON CONFLICT(destination_hash) DO UPDATE SET
last_read_at = EXCLUDED.last_read_at,
updated_at = EXCLUDED.updated_at
"""Tb4b4b4,
Tb4b4b4(Te6edf3destination_hashTb4b4b4, Te6edf3nowTb4b4b4, Te6edf3nowTb4b4b4, Te6edf3nowTb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffis_conversation_unreadTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4)Tb4b4b4:
Te6edf3row Tff7b72= Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(
T8b949e"""
SELECT m.timestamp, r.last_read_at
FROM lxmf_messages m
LEFT JOIN lxmf_conversation_read_state r ON r.destination_hash = ?
WHERE m.peer_hash = ? AND m.is_incoming = 1
ORDER BY m.timestamp DESC LIMIT 1
"""Tb4b4b4,
Tb4b4b4(Te6edf3destination_hashTb4b4b4, Te6edf3destination_hashTb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72if Tff7b72not Te6edf3rowTb4b4b4:
Tff7b72return Tff7b72False
Tff7b72if Tff7b72not Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6fflast_read_atTa5d6ff"Tb4b4b4]Tb4b4b4:
Tff7b72return Tff7b72True
Te6edf3last_read_at Tff7b72= Te6edf3datetimeTff7b72.Td2a8fffromisoformatTb4b4b4(Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6fflast_read_atTa5d6ff"Tb4b4b4]Tb4b4b4)
Tff7b72if Te6edf3last_read_atTff7b72.Td2a8fftzinfo Tff7b72is Tff7b72NoneTb4b4b4:
Te6edf3last_read_at Tff7b72= Te6edf3last_read_atTff7b72.Td2a8ffreplaceTb4b4b4(Te6edf3tzinfoTff7b72=Te6edf3UTCTb4b4b4)
Tff7b72return Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6fftimestampTa5d6ff"Tb4b4b4] Tff7b72> Te6edf3last_read_atTff7b72.Td2a8fftimestampTb4b4b4(Tb4b4b4)
Tff7b72def Td2a8ffmark_stuck_messages_as_failedTb4b4b4(Tff7b72selfTb4b4b4)Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
T8b949e"""
UPDATE lxmf_messages
SET state = 'failed', updated_at = ?
WHERE state = 'outbound'
OR (state = 'sent' AND method = 'opportunistic')
OR state = 'sending'
"""Tb4b4b4,
Tb4b4b4(Te6edf3datetimeTff7b72.Td2a8ffnowTb4b4b4(Te6edf3UTCTb4b4b4)Tff7b72.Td2a8ffisoformatTb4b4b4(Tb4b4b4)Tb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffget_failed_messages_for_destinationTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4)Tb4b4b4:
Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchallTb4b4b4(
Ta5d6ff"Ta5d6ffSELECT * FROM lxmf_messages WHERE state = Ta5d6ff'Ta5d6fffailedTa5d6ff'Ta5d6ff AND peer_hash = ? ORDER BY id ASCTa5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3destination_hashTb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffget_failed_messages_countTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4)Tb4b4b4:
Te6edf3row Tff7b72= Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(
Ta5d6ff"Ta5d6ffSELECT COUNT(*) as count FROM lxmf_messages WHERE state = Ta5d6ff'Ta5d6fffailedTa5d6ff'Ta5d6ff AND peer_hash = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3destination_hashTb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72return Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6ffcountTa5d6ff"Tb4b4b4] Tff7b72if Te6edf3row Tff7b72else T79c0ff0
Tff7b72def Td2a8ffget_conversations_unread_statesTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashesTb4b4b4)Tb4b4b4:
Tff7b72if Tff7b72not Te6edf3destination_hashesTb4b4b4:
Tff7b72return Tb4b4b4{Tb4b4b4}
Te6edf3placeholders Tff7b72= Ta5d6ff"Ta5d6ff, Ta5d6ff"Tff7b72.Td2a8ffjoinTb4b4b4(Tb4b4b4[Ta5d6ff"Ta5d6ff?Ta5d6ff"Tb4b4b4] Tff7b72* Tffa657lenTb4b4b4(Te6edf3destination_hashesTb4b4b4)Tb4b4b4)
Te6edf3query Tff7b72= Ta5d6fffTa5d6ff"""
Ta5d6ff SELECT peer_hash, MAX(timestamp) as latest_ts, last_read_at
Ta5d6ff FROM lxmf_messages m
Ta5d6ff LEFT JOIN lxmf_conversation_read_state r ON r.destination_hash = m.peer_hash
Ta5d6ff WHERE m.peer_hash IN (Tffd700{Te6edf3placeholdersTffd700}Ta5d6ff) AND m.is_incoming = 1
Ta5d6ff GROUP BY m.peer_hash
Ta5d6ff Ta5d6ff""" T8b949e# noqa: S608
Te6edf3rows Tff7b72= Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchallTb4b4b4(Te6edf3queryTb4b4b4, Te6edf3destination_hashesTb4b4b4)
Te6edf3unread_states Tff7b72= Tb4b4b4{Tb4b4b4}
Tff7b72for Te6edf3row Tff7b72in Te6edf3rowsTb4b4b4:
Te6edf3peer_hash Tff7b72= Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6ffpeer_hashTa5d6ff"Tb4b4b4]
Te6edf3latest_ts Tff7b72= Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6fflatest_tsTa5d6ff"Tb4b4b4]
Te6edf3last_read_at_str Tff7b72= Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6fflast_read_atTa5d6ff"Tb4b4b4]
Tff7b72if Tff7b72not Te6edf3last_read_at_strTb4b4b4:
Te6edf3unread_statesTb4b4b4[Te6edf3peer_hashTb4b4b4] Tff7b72= Tff7b72True
Tff7b72continue
Te6edf3last_read_at Tff7b72= Te6edf3datetimeTff7b72.Td2a8fffromisoformatTb4b4b4(Te6edf3last_read_at_strTb4b4b4)
Tff7b72if Te6edf3last_read_atTff7b72.Td2a8fftzinfo Tff7b72is Tff7b72NoneTb4b4b4:
Te6edf3last_read_at Tff7b72= Te6edf3last_read_atTff7b72.Td2a8ffreplaceTb4b4b4(Te6edf3tzinfoTff7b72=Te6edf3UTCTb4b4b4)
Te6edf3unread_statesTb4b4b4[Te6edf3peer_hashTb4b4b4] Tff7b72= Te6edf3latest_ts Tff7b72> Te6edf3last_read_atTff7b72.Td2a8fftimestampTb4b4b4(Tb4b4b4)
Tff7b72return Te6edf3unread_states
Tff7b72def Td2a8ffget_conversations_failed_countsTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashesTb4b4b4)Tb4b4b4:
Tff7b72if Tff7b72not Te6edf3destination_hashesTb4b4b4:
Tff7b72return Tb4b4b4{Tb4b4b4}
Te6edf3placeholders Tff7b72= Ta5d6ff"Ta5d6ff, Ta5d6ff"Tff7b72.Td2a8ffjoinTb4b4b4(Tb4b4b4[Ta5d6ff"Ta5d6ff?Ta5d6ff"Tb4b4b4] Tff7b72* Tffa657lenTb4b4b4(Te6edf3destination_hashesTb4b4b4)Tb4b4b4)
Te6edf3rows Tff7b72= Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchallTb4b4b4(
Ta5d6fffTa5d6ff"Ta5d6ffSELECT peer_hash, COUNT(*) as count FROM lxmf_messages WHERE state = Ta5d6ff'Ta5d6fffailedTa5d6ff'Ta5d6ff AND peer_hash IN (Tffd700{Te6edf3placeholdersTffd700}Ta5d6ff) GROUP BY peer_hashTa5d6ff"Tb4b4b4, T8b949e# noqa: S608
Tffa657tupleTb4b4b4(Te6edf3destination_hashesTb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72return Tb4b4b4{Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6ffpeer_hashTa5d6ff"Tb4b4b4]Tb4b4b4: Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6ffcountTa5d6ff"Tb4b4b4] Tff7b72for Te6edf3row Tff7b72in Te6edf3rowsTb4b4b4}
Tff7b72def Td2a8ffget_conversations_attachment_statesTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashesTb4b4b4)Tb4b4b4:
Tff7b72if Tff7b72not Te6edf3destination_hashesTb4b4b4:
Tff7b72return Tb4b4b4{Tb4b4b4}
Te6edf3placeholders Tff7b72= Ta5d6ff"Ta5d6ff, Ta5d6ff"Tff7b72.Td2a8ffjoinTb4b4b4(Tb4b4b4[Ta5d6ff"Ta5d6ff?Ta5d6ff"Tb4b4b4] Tff7b72* Tffa657lenTb4b4b4(Te6edf3destination_hashesTb4b4b4)Tb4b4b4)
Te6edf3query Tff7b72= Ta5d6fffTa5d6ff"""
Ta5d6ff SELECT peer_hash, 1 as has_attachments
Ta5d6ff FROM lxmf_messages
Ta5d6ff WHERE peer_hash IN (Tffd700{Te6edf3placeholdersTffd700}Ta5d6ff)
Ta5d6ff AND fields IS NOT NULL AND fields != Ta5d6ff'Tffea00{{Tffea00}}Ta5d6ff'Ta5d6ff AND fields != Ta5d6ff'Ta5d6ff'
Ta5d6ff GROUP BY peer_hash
Ta5d6ff Ta5d6ff""" T8b949e# noqa: S608
Te6edf3rows Tff7b72= Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchallTb4b4b4(Te6edf3queryTb4b4b4, Te6edf3destination_hashesTb4b4b4)
Tff7b72return Tb4b4b4{Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6ffpeer_hashTa5d6ff"Tb4b4b4]Tb4b4b4: Tff7b72True Tff7b72for Te6edf3row Tff7b72in Te6edf3rowsTb4b4b4}
T8b949e# Forwarding Mappings
Tff7b72def Td2a8ffget_forwarding_mappingTb4b4b4(
Tff7b72selfTb4b4b4,
Te6edf3alias_hashTff7b72=Tff7b72NoneTb4b4b4,
Te6edf3original_sender_hashTff7b72=Tff7b72NoneTb4b4b4,
Te6edf3final_recipient_hashTff7b72=Tff7b72NoneTb4b4b4,
Tb4b4b4)Tb4b4b4:
Tff7b72if Te6edf3alias_hashTb4b4b4:
Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(
Ta5d6ff"Ta5d6ffSELECT * FROM lxmf_forwarding_mappings WHERE alias_hash = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3alias_hashTb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3original_sender_hash Tff7b72and Te6edf3final_recipient_hashTb4b4b4:
Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(
Ta5d6ff"Ta5d6ffSELECT * FROM lxmf_forwarding_mappings WHERE original_sender_hash = ? AND final_recipient_hash = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3original_sender_hashTb4b4b4, Te6edf3final_recipient_hashTb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72return Tff7b72None
Tff7b72def Td2a8ffcreate_forwarding_mappingTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3dataTb4b4b4)Tb4b4b4:
T8b949e# Ensure data is a dict if it's a sqlite3.Row
Tff7b72if Tff7b72not Tffa657isinstanceTb4b4b4(Te6edf3dataTb4b4b4, Tffa657dictTb4b4b4)Tb4b4b4:
Te6edf3data Tff7b72= Tffa657dictTb4b4b4(Te6edf3dataTb4b4b4)
Te6edf3fields Tff7b72= Tb4b4b4[
Ta5d6ff"Ta5d6ffalias_identity_private_keyTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffalias_hashTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6fforiginal_sender_hashTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6fffinal_recipient_hashTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6fforiginal_destination_hashTa5d6ff"Tb4b4b4,
Tb4b4b4]
Te6edf3columns Tff7b72= Ta5d6ff"Ta5d6ff, Ta5d6ff"Tff7b72.Td2a8ffjoinTb4b4b4(Te6edf3fieldsTb4b4b4)
Te6edf3placeholders Tff7b72= Ta5d6ff"Ta5d6ff, Ta5d6ff"Tff7b72.Td2a8ffjoinTb4b4b4(Tb4b4b4[Ta5d6ff"Ta5d6ff?Ta5d6ff"Tb4b4b4] Tff7b72* Tffa657lenTb4b4b4(Te6edf3fieldsTb4b4b4)Tb4b4b4)
Te6edf3query Tff7b72= Ta5d6fffTa5d6ff"Ta5d6ffINSERT INTO lxmf_forwarding_mappings (Tffd700{Te6edf3columnsTffd700}Ta5d6ff, created_at) VALUES (Tffd700{Te6edf3placeholdersTffd700}Ta5d6ff, ?)Ta5d6ff" T8b949e# noqa: S608
Te6edf3params Tff7b72= Tb4b4b4[Te6edf3dataTff7b72.Td2a8ffgetTb4b4b4(Te6edf3fTb4b4b4) Tff7b72for Te6edf3f Tff7b72in Te6edf3fieldsTb4b4b4]
Te6edf3paramsTff7b72.Td2a8ffappendTb4b4b4(Te6edf3datetimeTff7b72.Td2a8ffnowTb4b4b4(Te6edf3UTCTb4b4b4)Tff7b72.Td2a8ffisoformatTb4b4b4(Tb4b4b4)Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Te6edf3queryTb4b4b4, Te6edf3paramsTb4b4b4)
Tff7b72def Td2a8ffget_all_forwarding_mappingsTb4b4b4(Tff7b72selfTb4b4b4)Tb4b4b4:
Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchallTb4b4b4(Ta5d6ff"Ta5d6ffSELECT * FROM lxmf_forwarding_mappingsTa5d6ff"Tb4b4b4)
Tff7b72def Td2a8ffmark_notification_as_viewedTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4)Tb4b4b4:
Te6edf3now Tff7b72= Te6edf3datetimeTff7b72.Td2a8ffnowTb4b4b4(Te6edf3UTCTb4b4b4)Tff7b72.Td2a8ffisoformatTb4b4b4(Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
T8b949e"""
INSERT INTO notification_viewed_state (destination_hash, last_viewed_at, created_at, updated_at)
VALUES (?, ?, ?, ?)
ON CONFLICT(destination_hash) DO UPDATE SET
last_viewed_at = EXCLUDED.last_viewed_at,
updated_at = EXCLUDED.updated_at
"""Tb4b4b4,
Tb4b4b4(Te6edf3destination_hashTb4b4b4, Te6edf3nowTb4b4b4, Te6edf3nowTb4b4b4, Te6edf3nowTb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffmark_all_notifications_as_viewedTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashesTff7b72=Tff7b72NoneTb4b4b4)Tb4b4b4:
Te6edf3now Tff7b72= Te6edf3datetimeTff7b72.Td2a8ffnowTb4b4b4(Te6edf3UTCTb4b4b4)Tff7b72.Td2a8ffisoformatTb4b4b4(Tb4b4b4)
Tff7b72if Te6edf3destination_hashesTb4b4b4:
Tff7b72with Tff7b72selfTff7b72.Td2a8ffproviderTb4b4b4:
Tff7b72for Te6edf3destination_hash Tff7b72in Te6edf3destination_hashesTb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
T8b949e"""
INSERT INTO notification_viewed_state (destination_hash, last_viewed_at, created_at, updated_at)
VALUES (?, ?, ?, ?)
ON CONFLICT(destination_hash) DO UPDATE SET
last_viewed_at = EXCLUDED.last_viewed_at,
updated_at = EXCLUDED.updated_at
"""Tb4b4b4,
Tb4b4b4(Te6edf3destination_hashTb4b4b4, Te6edf3nowTb4b4b4, Te6edf3nowTb4b4b4, Te6edf3nowTb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72elseTb4b4b4:
T8b949e# mark all conversations as viewed
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
T8b949e"""
INSERT INTO notification_viewed_state (destination_hash, last_viewed_at, created_at, updated_at)
SELECT peer_hash, ?, ?, ? FROM lxmf_messages
WHERE peer_hash IS NOT NULL
GROUP BY peer_hash
ON CONFLICT(destination_hash) DO UPDATE SET
last_viewed_at = EXCLUDED.last_viewed_at,
updated_at = EXCLUDED.updated_at
"""Tb4b4b4,
Tb4b4b4(Te6edf3nowTb4b4b4, Te6edf3nowTb4b4b4, Te6edf3nowTb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffis_notification_viewedTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4, Te6edf3message_timestampTb4b4b4)Tb4b4b4:
Te6edf3row Tff7b72= Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(
Ta5d6ff"Ta5d6ffSELECT last_viewed_at FROM notification_viewed_state WHERE destination_hash = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3destination_hashTb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72if Tff7b72not Te6edf3row Tff7b72or Tff7b72not Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6fflast_viewed_atTa5d6ff"Tb4b4b4]Tb4b4b4:
Tff7b72return Tff7b72False
Te6edf3last_viewed_at Tff7b72= Te6edf3datetimeTff7b72.Td2a8fffromisoformatTb4b4b4(Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6fflast_viewed_atTa5d6ff"Tb4b4b4]Tb4b4b4)
Tff7b72if Te6edf3last_viewed_atTff7b72.Td2a8fftzinfo Tff7b72is Tff7b72NoneTb4b4b4:
Te6edf3last_viewed_at Tff7b72= Te6edf3last_viewed_atTff7b72.Td2a8ffreplaceTb4b4b4(Te6edf3tzinfoTff7b72=Te6edf3UTCTb4b4b4)
Tff7b72return Te6edf3message_timestamp Tff7b72<Tff7b72= Te6edf3last_viewed_atTff7b72.Td2a8fftimestampTb4b4b4(Tb4b4b4)
T8b949e# Folders
Tff7b72def Td2a8ffget_all_foldersTb4b4b4(Tff7b72selfTb4b4b4)Tb4b4b4:
Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchallTb4b4b4(Ta5d6ff"Ta5d6ffSELECT * FROM lxmf_folders ORDER BY name ASCTa5d6ff"Tb4b4b4)
Tff7b72def Td2a8ffcreate_folderTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3nameTb4b4b4)Tb4b4b4:
Te6edf3now Tff7b72= Te6edf3datetimeTff7b72.Td2a8ffnowTb4b4b4(Te6edf3UTCTb4b4b4)Tff7b72.Td2a8ffisoformatTb4b4b4(Tb4b4b4)
Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffINSERT INTO lxmf_folders (name, created_at, updated_at) VALUES (?, ?, ?)Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3nameTb4b4b4, Te6edf3nowTb4b4b4, Te6edf3nowTb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffrename_folderTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3folder_idTb4b4b4, Te6edf3new_nameTb4b4b4)Tb4b4b4:
Te6edf3now Tff7b72= Te6edf3datetimeTff7b72.Td2a8ffnowTb4b4b4(Te6edf3UTCTb4b4b4)Tff7b72.Td2a8ffisoformatTb4b4b4(Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffUPDATE lxmf_folders SET name = ?, updated_at = ? WHERE id = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3new_nameTb4b4b4, Te6edf3nowTb4b4b4, Te6edf3folder_idTb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffdelete_folderTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3folder_idTb4b4b4)Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Ta5d6ff"Ta5d6ffDELETE FROM lxmf_folders WHERE id = ?Ta5d6ff"Tb4b4b4, Tb4b4b4(Te6edf3folder_idTb4b4b4,Tb4b4b4)Tb4b4b4)
Tff7b72def Td2a8ffget_conversation_folderTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3peer_hashTb4b4b4)Tb4b4b4:
Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(
Ta5d6ff"Ta5d6ffSELECT * FROM lxmf_conversation_folders WHERE peer_hash = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3peer_hashTb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffmove_conversation_to_folderTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3peer_hashTb4b4b4, Te6edf3folder_idTb4b4b4)Tb4b4b4:
Te6edf3now Tff7b72= Te6edf3datetimeTff7b72.Td2a8ffnowTb4b4b4(Te6edf3UTCTb4b4b4)Tff7b72.Td2a8ffisoformatTb4b4b4(Tb4b4b4)
Tff7b72if Te6edf3folder_id Tff7b72is Tff7b72NoneTb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffDELETE FROM lxmf_conversation_folders WHERE peer_hash = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3peer_hashTb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72elseTb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
T8b949e"""
INSERT INTO lxmf_conversation_folders (peer_hash, folder_id, created_at, updated_at)
VALUES (?, ?, ?, ?)
ON CONFLICT(peer_hash) DO UPDATE SET
folder_id = EXCLUDED.folder_id,
updated_at = EXCLUDED.updated_at
"""Tb4b4b4,
Tb4b4b4(Te6edf3peer_hashTb4b4b4, Te6edf3folder_idTb4b4b4, Te6edf3nowTb4b4b4, Te6edf3nowTb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffmove_conversations_to_folderTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3peer_hashesTb4b4b4, Te6edf3folder_idTb4b4b4)Tb4b4b4:
Tff7b72with Tff7b72selfTff7b72.Td2a8ffproviderTb4b4b4:
Tff7b72for Te6edf3peer_hash Tff7b72in Te6edf3peer_hashesTb4b4b4:
Tff7b72selfTff7b72.Td2a8ffmove_conversation_to_folderTb4b4b4(Te6edf3peer_hashTb4b4b4, Te6edf3folder_idTb4b4b4)
Tff7b72def Td2a8ffget_all_conversation_foldersTb4b4b4(Tff7b72selfTb4b4b4)Tb4b4b4:
Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchallTb4b4b4(Ta5d6ff"Ta5d6ffSELECT * FROM lxmf_conversation_foldersTa5d6ff"Tb4b4b4)
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────